package org.nokatag.dbgrid;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.nokatag.system.BugInfoWrint;

public class NDBGridDB2 extends NDBGridData{
	public NDBGridDataItem DataQuery(NDBGridDataQueryItem dq,Connection con) {
		try{
			//----------------------加载查询条件-----------------------------------------------------------
			String sql=dq.getSql();
			if(dq.getWsql()!=null)
				sql="select   * from ("+sql+")  temptable_"+System.currentTimeMillis()+" where 1=1 "+ dq.getWsql();
			else
				sql="select   * from ("+sql+")  temptable_"+System.currentTimeMillis()+" where 1=1 ";
			//----------------------总记录数--------------------------------------------------------------
			BugInfoWrint.Print("DB2:"+sql);
			PreparedStatement pa=null;
			ResultSet rsf=null;
			Long lastRow = 0L;
			try{
				pa= con.prepareStatement(" select count(1) as conet from  ("+ sql + ")  temptable_" + System.nanoTime(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				for(CellParItem c:dq.getParList()){
					//pa.setObject(c.getIndex(),c.getValue(),c.getType());
					setPares(pa,c.getIndex(),c.getValue(),c.getType());
				}
				rsf=pa.executeQuery();
				rsf.next();
				lastRow = rsf.getLong("conet");// 一共有多少条记录
			}catch(Exception se){
			}finally{
				if(null!=pa){
					try{pa.close();}catch(Exception se){}
				}
				if(null!=rsf){
					try{rsf.close();}catch(Exception se){}
				}
			}
			//----------------------获取数据--------------------------------------------------------------
			Long showPage =dq.getShowPage();
			Long pageSize =dq.getPageSize()==null?(lastRow<1?1:lastRow):dq.getPageSize();
			Long pageConut = (lastRow % pageSize == 0) ? (lastRow / pageSize): (lastRow / pageSize + 1);// 总的页数
			if (showPage > pageConut)
				showPage = pageConut;
			if (showPage < 1)
				showPage = 1L;
			Long posion = (showPage - 1) * pageSize+1;// 游标位置
			List<CellParItem> cellName = dq.getCellName();
			String cname="";
			for (int s = 0; s < cellName.size(); s++) {
				cname+=","+cellName.get(s).getCellname();
			}
			String selectSql = "SELECT * FROM (Select "+cname.substring(1)+",rownumber() over() AS rn from ("+sqlpx(dq,sql)+") AS B ) AS a1 WHERE a1.rn BETWEEN "+posion+" AND "+(posion+pageSize-1)+" ";
			
			PreparedStatement pa2= null;
			ResultSet rss =null;
			List<Map<String,String>> rows = null;
			List<String> cellnames=null;
			try{
			
				pa2= con.prepareStatement(selectSql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				for(CellParItem c:dq.getParList()){
					//pa2.setObject(c.getIndex(),c.getValue(),c.getType());
					setPares(pa2,c.getIndex(),c.getValue(),c.getType());
				}
				rss = pa2.executeQuery();
				ResultSetMetaData rsmd = rss.getMetaData();// 取得列名
				rows = new ArrayList<Map<String,String>>();
				cellnames=new ArrayList<String>();//列名 
				while (rss.next()) {
					Map<String,String> row=new HashMap<String,String>();
					Map<String, Object> map=new HashMap<String, Object>();//列名=值
					cellnames=new ArrayList<String>();//列名 
					for(int a=1;a<=rsmd.getColumnCount();a++){
						cellnames.add(rsmd.getColumnName(a));//列名 
						String cellvalue= frmotDate(rsmd.getColumnType(a),rss,a);
						map.put(rsmd.getColumnName(a), cellvalue);
					}
					for(int a=0;a<cellnames.size();a++){
						String value=formatValue(map.get(cellnames.get(a)),dq.getFormatcell(),cellnames.get(a),dq.getRequest(),map);
						row.put(cellnames.get(a), value);
					}
					rows.add(row);
				}
			}catch(Exception se){
			}finally{
				if(null!=pa2){
					try{pa2.close();}catch(Exception se){}
				}
				if(null!=rss){
					try{rss.close();}catch(Exception se){}
				}
			}
			//---------------------汇总列-----------------------------------------------------------------
			String sumcells = dq.getSumcells();
			Map<String,String> sumCells= new HashMap<String,String>();//汇总列
			if(null!=sumcells && !"-1".equals(sumcells)){
				String[] cidex = sumcells.split(",");
				String sum_cell = "*";
				for(String idex:cidex){
					try{
						int cid = Integer.parseInt(idex);
						String scellName = cellnames.get(cid);//汇总的列名
						if(null!=scellName && scellName.trim().length()>0){
							sum_cell+=",sum("+scellName+") as "+scellName;
						}
					}catch(Exception ex){}
				}
				if(!"*".equals(sum_cell)){
					sum_cell=sum_cell.substring(2);
				}
				String sumSQL = "select "+sum_cell+" from ("+dq.getSumsql()+")  temptable_"+System.currentTimeMillis();
				PreparedStatement pa_sum = null;
				ResultSet rs_sum = null;
				try{
					BugInfoWrint.Print("Data DB2 sumSQL:"+sumSQL);
					pa_sum= con.prepareStatement(sumSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
					for(CellParItem c:dq.getParList()){
						//pa_sum.setObject(c.getIndex(),c.getValue(),c.getType());
						setPares(pa_sum,c.getIndex(),c.getValue(),c.getType());
					}
					rs_sum=pa_sum.executeQuery();
					ResultSetMetaData rsmd_sum = rs_sum.getMetaData();// 取得列名
					if(rs_sum.next()){
						Map<String, Object> map_sum=new HashMap<String, Object>();//列名=值
						List<String> cellnames_sum=new ArrayList<String>();//列名 
						for(int a=1;a<=rsmd_sum.getColumnCount();a++){
							cellnames_sum.add(rsmd_sum.getColumnName(a));//列名 
							map_sum.put(rsmd_sum.getColumnName(a), rs_sum.getString(a));
						}
						for(int a=0;a<cellnames_sum.size();a++){
							String value=formatValue(map_sum.get(cellnames_sum.get(a)),dq.getSumformatcell(),cellnames_sum.get(a),dq.getRequest(),map_sum);
							sumCells.put(cellnames_sum.get(a), value);
						}
					}
				}catch(Exception ex){
				}finally{
					if(null!=pa_sum){
						try{pa_sum.close();}catch(Exception se){}
					}
					if(null!=rs_sum){
						try{rs_sum.close();}catch(Exception se){}
					}
				}
			}
			//--------------------------------------------------------------------------------------
			NDBGridDataItem nd = new NDBGridDataItem(pageConut, showPage, posion, (posion+rows.size()-1), Long.valueOf(rows.size()),lastRow,rows,cellnames);
			nd.setSumCells(sumCells);
			return nd;
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}
		return null;
	}
}
